/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package src.com.dados.matricula;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import src.com.dados.infraEstrutura.UnidadeDao;
import src.com.dados.pedagogico.AlunoDao;
import src.com.dados.pedagogico.NivellDao;
import src.com.dados.pedagogico.ProfessorDao;

/**
 *
 * @author Giane
 */
public class AgendaDao {

    public void inserirNivel(String professor,String nome,String CPF,String idioma, String uni, String d_data,String horaInicio,String horaFim) throws SQLException {
        
            AlunoDao s = new AlunoDao();
            ProfessorDao t = new ProfessorDao();
            NivellDao u = new NivellDao();
            UnidadeDao v = new UnidadeDao();
            
            String id_aluno = s.id_aluno(nome, CPF);
            String id_prof = t.id_professor(professor);
            String id_idioma = u.id_idioma(idioma);
            String id_uni = v.id_unidade(uni);
            
            
            Connection conexao;
            String inserir_agenda = "INSERT INTO `bdeng2`.`tst_teste_nivelamento` (`tst_professor_id`, `tsts_aluno_id`, `tst_idioma_id`, `tst_unidade_id`, `tst_data`, `tst_hora_inicio`, `tst_hora_fim`) VALUES ('"+
                                     id_prof+"','"+id_aluno+"','"+id_idioma+"','"+id_uni+"','"+d_data+"','"+horaInicio+"','"+horaFim+"');";
            System.out.print(inserir_agenda);
            
            String url = "jdbc:mysql://localhost/bdeng2";
            DriverManager.registerDriver(new com.mysql.jdbc.Driver());
            conexao = DriverManager.getConnection(url, "root", "dados");


            Statement stmt2 = (Statement) conexao.createStatement();
            stmt2.execute(inserir_agenda);
            conexao.close();
        
    }

    public ArrayList<String> consultarData(String dt) throws SQLException {
                Connection conexao;
        ArrayList<String> result = new ArrayList<String>();
        String select_ano = "select a.pes_nome, idm_descricao ,p.pes_nome, tst_hora_inicio from pes_pessoa p, pes_pessoa a, prof_professor pr,alu_aluno al, tst_teste_nivelamento, idm_idioma,und_unidade where tst_professor_id = prof_id and tsts_aluno_id = alu_matricula and p.pes_id = pr.pes_pessoa_pes_id and  a.pes_id = al.pes_pessoa_pes_id and tst_idioma_id = idm_id and tst_unidade_id = und_unidade_id and tst_data = '"+dt+"';";
        String url = "jdbc:mysql://localhost/bdeng2";
        DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        //Class.forName("com.mysql.jdbc.Driver");
        conexao = DriverManager.getConnection(url, "root", "dados");
        System.out.println(" -------------- " + select_ano);

        PreparedStatement stmt = conexao.prepareStatement(select_ano);
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            result.add(rs.getString(1));
            result.add(rs.getString(2));
            result.add(rs.getString(3));
            result.add(rs.getString(4));
        }

        conexao.close();
        return result;


    }

    public String verificaAgendamento(String d_data, String horaInicio, String horaFim, String professor) throws SQLException {
       String select_count = null;

        select_count = "select count(tst_id) from tst_teste_nivelamento, prof_professor,pes_pessoa where tst_professor_id = prof_id and pes_pessoa_pes_id = pes_id and pes_nome = '"+professor+"' and tst_data = '"+d_data+"' and tst_hora_inicio = '"+horaInicio+"' and tst_hora_fim = '"+horaFim+"';  ";


        Connection conexao;
        String result = "0";


        String url = "jdbc:mysql://localhost/bdeng2";
        DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        conexao = DriverManager.getConnection(url, "root", "dados");
        PreparedStatement stmt = conexao.prepareStatement(select_count);
        ResultSet rs = stmt.executeQuery();


        while (rs.next()) {
            result = rs.getString(1);
        }


        conexao.close();
        return result;
    }

    public void agendaraula(String aluno, String CPF, String id_turma) throws SQLException {
        Connection conexao;
            String inserir_agenda = "INSERT INTO `bdeng2`.`ald_aula_demonstrativa` (`ald_nm_aluno`, `ald_cpf`, `tur_turma_tur_id`) VALUES ('"+aluno+"', '"+CPF+"', "+id_turma+");";
            System.out.print(inserir_agenda);
            
            String url = "jdbc:mysql://localhost/bdeng2";
            DriverManager.registerDriver(new com.mysql.jdbc.Driver());
            conexao = DriverManager.getConnection(url, "root", "dados");


            Statement stmt2 = (Statement) conexao.createStatement();
            stmt2.execute(inserir_agenda);
            conexao.close();
        

    }
    
}
